SQL Server 2021 年 9 月 7 日

【SQL Server】第一章、SQL Server 基础

SQL Server

第一章、SQL Server 基础

1、SQL Server简介

微软宣布了SQL Server 2016，SQL Server是由微软开发的==关系型数据库==管理系统，用于软件应用请求数据的存储和管理。

SQL Server 2016分为==数据库引擎==、==分析服务==、==集成服务==和==报表服务==等

SQL Server 2016将提供更好的数据安全性能，新增名为Stretch Database的功能，并将具有超快的事务性能等等。以下是SQL Server 2016的一些主要新特性：

增强的安全性
改进AlwaysOn可以性及灾难可恢复性
原生JSON数据支持，为多种类型数据提供更好的支持
SQL Server企业管理信息（EIM）工具和分析服务的性能，可用性和可扩展性得到提升
更快的hybrid备份

数据库引擎

SQL Server 2016数据库引擎是SQL Server 2016的核心服务，负责完成数据的存储、处理和安全管理。包括数据库引擎（用于存储、处理和保护数据的核心服务）、复制、全文搜索以及用于管理关系数据和XML数据的工具。

分析服务

主要是通过服务器和客户端技术的组来提供联机分析处理（On-Line-Analytical Processing,OLAP）和数据挖掘功能。

集成服务（Interaction Server）

SQL Server 2016是一个用于生成高性能数据集成和工作流解决方案的平台，负责完成数据提取、转换和加载等操作。

报表服务

主要用于创建和发布报表及报表模型的图形工具和向导、管理Reporting Service的报表服务器管理工具，以及对Reporting Service对象模型进行编程和扩展的应用程序编程接口。

SQL Server 数据库的构成

RDBMS（RElational Database Management System）关系数据库管理系统，现在高级的RDBMS不仅可以存储数据，而且可以管理数据，如限定输入到系统的数据类型，还可以方便地从系统中读取数据。

数据库对象概述

RDBMS包含许多对象，包括：

数据库本身
事务日志
表
索引
数据库关系图
视图
存储过程
用户自定义函数
序列
用户
角色
程序集
报表
全文目录
用户自定义数据类型

SSMS

登陆

数据库

2、数据对象

在SQL Server中，数据库对象可以看作是最高层的对象，虽然服务器本身可以看作一个对象；但是在实际编程的角度看，一般不会把数据服务器看出对象。在SQL Server中大部分其他对象，可以看作是数据库的子对象。

数据库通常包含至少一组表对象，一般也包括其他一些对象，如存储过程和视图；存储过程和视图是对存储在数据库表中的特定分组。

EDBMS在同一台服务器上可以有多个用户数据库，当然也可能只有一个用户数据库。单个SQL Server上可以容纳多少数据库的决定因素有（CPU、磁盘、内存等）、自主权或实际需要。

现在使用的各个版本的SQL Server都可以在同一台服务器上，存在多个数据库实例，而且有单独的登录和管理权限。

数据库操作

数据库：即具有管理和保管数据能力的仓库。

数据存储结构：

==逻辑存储结构==
- 不仅说明存储数据的信息，而且所有与数据处理操作相关的信息，都存储在数据库中
==物理存储结构==
- 讨论数据库文件在磁盘中是如何存储的。数据库在磁盘上是以文件为单位存储，由数据库文件和事务日志文件构成，一个数据至少应包含一个数据文件和一个事务日志文件

数据库文件

指数据库中用来存放数据库数据和数据库对象的文件，一个数据库可以有一个或多个数据库文件，一个数据库文件，只能属于一个数据库。当有多个数据文件存在时，会有一个数据库文件被定义为主数据库文件，它用来存储数据库的启动信息和部分或全部数据，一个数据库只能有一个主数据库文件，其他的是此数据库文件。

==数据库文件会划分为不同的页或区域，页是SQL Server 存储数据的基本单位。==

主数据库文件

数据库的起点，包括数据库的启动信息是指向数据库文件的其他部分，每个数据库都有一个主数据库文件，其扩展名名为==.mdf==。在创建数据库时，如果选择的文件组是“Primary”，这就是主数据库文件。（由于文件组可选值有两个，一个是Primary是主数据库文件，而Second就是此数据文件）

次数据库文件

包含除主数据库文件外的所有数据文件，一个数据库可以有多个或密钥次数据库文件，但必须一个主数据库文件。

创建数据库时，默认，数据库逻辑文件名和物理文件名是一样的。而这时两个文件名，可以各自知道指定。

逻辑文件名：==是引用文件名时，使用的名称==

物理文件名：==物理文件的名称==

数据库名称不能包含以下不允许使用的非法字符：==“ ’ * / ？：、 < > -== 这些非法字符

3、系统数据库

所有者

指的是任何一个拥有创建数据库权限的用户，这里用默认值就可以

创建数据库时“选项”选项卡的设置

恢复模式

完整：允许发生错误时恢复数据库，在发生错误时，可以即时的使用日志事务日志恢复数据库。

大容量日志：当执行操作的数据量比较大时，只记录该操作事件，并不记录插入的细节。如：向数据库插入大量的记录时，只记录该操作事件，并不记录每一行插入的的内容。这样一来可以提高系统性能，但是一旦发生错误时，只能恢复最后一次备份的日志的内容。

简单：每次备份数据库时清除事务日志，表示根据最后一次对数据库的备份进行恢复。

兼容级别

兼容级别：是否允许建立一个兼容早期版本的数据库，==如要兼容早期版本SQL Server，则新版本中的一些功能将不能使用==

文件组

在文件组里，可以设置添加的数据库文件是哪个文件组（可选项有Primary和Second）

SQL Server 自带数据库

==Master==
==Model==
==Msdb==
==Tempdb==

根据安装的选项不同，还有一些样本数据库，如：

ReportServer：为Reporting Server配置和模型存储需求提供服务的数据
ReportServerTempDB：用于Reporting Server的工作数据库

master

任何 SQL Server 都有master数据库，而不管是哪一个版本。

Master数据库保存一组特定的表（系统表）用于系统的总体控制

如在服务器上新建一个数据库，将会在master数据的sysdatabases视图中放入对应的条目。所有的扩展存储过程和系统存储过程都在存储在master数据库空中，而不是该存储过程是属于哪一个数据库的。

Model

Model数据库构成任何新建数据库的模板。换句话说，如果需要改变新家数据库的样式，可以更改model数据库。由于model数据库可以作为其他任何数据的模板，因此是不允许被删除的。

修改model数据库要注意：

任意新建的数据库都要比model数据库大，换句话说。如果model是200m，那么就不能新建小于200m的数据库。
添加对象或更改设置时，要注意，因为这会影响，你将要新建的所有数据库

msdb

SQL Agent进程存储任意系统任务的地方。如计划对一个数据库在每天指定时间备份，则msdb数据库中有一个记录项；如每次执行任务时调度存储过程，则在msdb数据库中会有一个记录项。

tempdb

Tempdb数据库是服务器主要工作区域之一，在执行一个复杂或大型的查询操作时SQL Server可能需要创建一些中间表来完成该操作，这项工作就是tempdb数据库中进行的，只要创建自己的临时表，这些表就会创建在tempdb数据库中。

4、管理数据库

主要包含修改数据库、查看数据库信息、数据库信息、数据库更名和删除数据库，这里主要讲的是在资源管理器对数据库进行修改，以后还会将用T-SQL语句进行操作。

修改数据库

指定数据库名称上右键-属性，打开“数据库属性”窗口，该窗口与在SSMS中创建数据库时打开的窗口相似，不过多了几个选项。包括：更改跟踪、权限、扩展属性、镜像和事务日志传送，这里可以根据需要自行设置。

修改容量大小、即在属性窗口中进行修改

更改数据库容量的最大限制

5、管理数据表

管理数据表

修改字段，在修改字段之前需要进行设置。在“工具”->“选项”->去掉“阻止保存需要重新创建表的更改”前的对勾

增加字段

删除字段

修改表约束

查看表中的相关信息

修改数据表

查看表的结构
查看表的相关信息（表右键属性）
查看表中存储的数据
查看表与其他数据对象的依赖关系（表右键查看依赖关系）
更改数据库表字段增长值
查看数据库信息

SQL Server中可以使用多种方式查看数据库信息，如目录视图、函数、存储过程等

目录视图

sys.database_files 查看有关数据库文件的信息
sys.filegroups 查看有关数据库组的信息
sys.master_files 查看数据库文件的基本你西南西和状态信息
sys.databases 数据库和文件目录视图查看有关数据库的基本信息

6、如何使用存储过程

使用存储过程进行查询

sp_spaceused 查询当前数据库的信息
sp_helpdb 查询所有数据的信息

==EXEC 存储过程==

可以在数据库属性“选项”选项卡上进行查看

数据类型

8位 = 1字节

1024字节 = 1K

1024K = 1M

1024M = 1G

1024G = 1T

整数数据类型

==Tinyint== ：每个tinyint类型的数据占用一个字节的存储空间，要以表示0到255的范围内的所有整数。

==Smallint== ：每个smallint类型的数据占有两个字节的存储空间，可以表示0~255范围内的所有整数。

==Int== ：每个int存储在4个字节中，其中一个2进制位表视符号，其他31个二进制位表视长度和大小，可以表示-231~231-1范围内的所有整数。

==Bigint== ：每个bigint存储在8个字节中，其中1个二进制和位表示符号，其他63个二进制位表示长度和大小，可以表示-263~263-1范围内的所有整数。

浮点数据类型

浮点型数据存储十进制小数，有表示浮点数值数据的大致数值数据类型，浮点数据为近似值

==real== ：可以存储正的或者负的十进制值，他的存储范围从3.40E+38~~1.18E-38、0以及1.18E~~38~3.40E+38。每个real’类型的数据占用四个字节的存储空间。

==float[(n)]== ：其中n为用于存储float数值位数的位数（以科学计数法表示），n必须介于1到53之间的值，默认值为53。

字符数据类型

字符数据类型也是SQL Server中最常用的数据类型之一，用来存储各种字母、数字符号和特殊字符。在使用字符数据类型时，要加上引号（单引号或者双引号）

==char(n)== ：当用char数据类型存储数据时，每个字符和符号占用一个字节的存储空间。n表示所有字符所咱的存储空间，n的取值为1-8000。若不指定n值，系统默认值为1。若输入数据的字符串长度小于n，则系统自动在其后添加空格来填满设定好的空间；若输入的数据超过n，将会截掉多出来的字符。

==Nvarchar(n|Max)==

varchar相似，存储可变长度Unicode字符数据。N值在1到4000之间，如果没有在数据定义或变量声明语句中指定n，默认长度为1。Max指示最大存储大小为2的31次方~11个字节。所输入的数据的长度可以为0个字符。

日期和时间数据类型

==Date== ：存储用字符串表示的日期数据，可以表示0001-01-01到999-12-31间的任意日期值。数据格式为”YYYY-MM-DD”：

YYYY ：表示年份的四位数字，那位0001-9999
MM ：表示指定年份中的月份的两位数字，范围为01-12
DD ：表示指定月份中的某一天的两位数字，范围为0-31（最高值取决于具体月份）

该类型占用三个字节空间。

==Time== ：以字符串形式记录一天中的某个时间，取值范围为00:00:00.0000000~23:59:59.0000000，数据格式为”hh:mm:ss[.nnnnnnn]“：

hh ：表示小时是的两位数字，范围为0~23
mm ：表示分钟的两位数字，范围为0~59
ss ：表示秒的两位数字，范围为0~59
n* ：是0到7位数字，范围为0~9999999，它表示秒的小数部分

Time值存储时占用5个字节的空间

==Datetime== ：用于存储时间和日期数据，从1753年的1月1日到9999年12月31日，默认值1900-01-01 00:00:00，当插入数据或其他地方时，需要单引号或双引号括起来。可以使用“/”、“-”和“.”作为分隔符。该数据类型占用8个字节的空间。

==Datetime2== ：Datetime类型的扩展，其数据范围更大，默认的小数精度更高，并具有可选的用户定义的精度。默认格式为：yyyy-mm-dd hh:mm:ss[.fracetional seconds]，日期存取范围是0001-01-01~9999-12-31

==Smalldatetime== ：与datetime类型相似，只是其存取的范围是从1900年1月1日到2079年6月6日，当日期时间精度较小时，可以使用smalldatetime，该类型数据占用4个字节的空间

==Datetimeoffset== ：用于定义一个采用24小时格式制与日期相结合并可识别时区的一日内时间。默认格式：yyyy-mm-dd hh:mm:ss[.nnnnnnn][{+|-}hh:mm]：

hh ：两位数，范围为-14~+14
mm ：两位数，范围为00~59

这里hh是时区偏移量，该类型数据中保存的是世界标准市区（UTC）值，例如要存储北京时间2015年12月12日12点整，存储时该值将是2015-12-12 12:00:00+08:00，因为北京处于东八区，比UTC早八个小时。存储该类型数据时默认占用10个字节大小的固定存储空间。

文本和图形数据类型

==Text== ：用于存储文本数据，服务器代码页中长度可变的非Unicode数据，最大长度为2的31次方-1（2127483647）个字符。当服务器代码页使用双字节字符时，存储仍是2的31次方-1（2127483647）个字符。

==Ntext== ：与text类型作用相同，为长度可变的Unicode数据，最大长度为2的30次方-1（1073741823）个字符。存储大小是所输入字符个数的两倍（以字节为单位）。

==image== ：长度可变的二进制数据，从0~2的31次方-1个字节。用于存储照片、目录图片或图画，容量也是2147483647个字节，由系统根据数据的长度自动分配空间，存储字段的数据一般不能使用Inssert语句直接输入。

货币数据类型

==Money== ：用于存储货币的值，取值范围为正负922337213685477.5808之间。Money数据类型中整数部分包含19个数字，小数部分包含4位数字，因此money数据类型的精度是19，存储时占用8个字节的粗出空间。

7、SQL Server 用户登录

利用SQL Server用户登录

在数据库服务器上“右键”》“属性”》选择“安全性”选项》“Windows和SQL Server混合身份验证”
在“安全性”》先选择指定用户如“sa”设置密码；并将该用户的状态设置为启用
重启MSSQLLSERVER服务

8、什么是约束

约束方法

唯一约束、主键约束、标识列

参数完整性：一个表的外键可以为空值。如果不为空值，则每一个外键值必须必须等于相关联的另外那张表中主键的某个值。

外键约束：

用户定义完整性：它是设计者为了保证表中某些行或者列的数据满足具体应用需求而自定义的一些规则。

检查约束、存储过程、触发器

SQL Server创建表的过程就是规定数据列的属性过程，同时也是实时数据完整性约束的过程。

创建数据表需要确定表的列名、数据类型、是否允许为空，还需要确定主键、必要的默认值、标识列和检查约束。

表示用来存储数据和操作数据的逻辑结构，用来组织和存储数据，关系数据库中的所有数据都表现为表的形式，数据表由行和列的组成，对数据库的操作，基本上就是对数据表的操作。

SQL Server中的数据表分为临时表的和永久表，临时表存储在tempdb系统数据库中，当不再使用或退出SQL Server时，临时表会自动删除；而永久表一旦创建之后，除非用户删除，否则将一直存放在数据库文件中。SQL Server 2016中有两种创建数据表的方法：一种是通过对象资源浏览器创建，另一种是通过Transact-SQL语句创建。

修改表的约束

约束是用来保证数据库完整性的一种方法，设计表时，需要定义列的有效值并通过限制字段中数据、记录中数据和表之间的数据来保证数据的完整性，约束是独立于表结构的，它作为数据库定义的一部分在创建表时声明，可以通过对象资源管理器或者Alter table语句添加和删除。

SQL Server 2016 中有五种约束，分别是：

==主键约束（Primary key constraint）==
==唯一性约束（unique constraint）==
==检查约束（check constraint）==
==默认约束（default constraint）==
==外键约束（foreign key constraint）==

主键约束

主键约束（==Primary key constraint==）可以在表中定义一个主键值，它可以唯一确定表中每一条记录，也是重要的一种约束。每个表中只能有一个Primary Key约束，并且Primary Key约束的列不能接受空值。如果主键约束定义在不止一列上，则一列中的值可以重复，但主键约束定义中，所有列的组合值必须唯一。

唯一性约束

唯一性约束（==Unique==）确保在非主键列中不输入重复的值。用于指定一个或者多个列的组合值具有唯一性，以防止在列中输入重复的值。可以对一个表定义多个unique约束，但只能定义一个primary key约束。unique约束允许NULL值，但是当和参与unique约束的任何值一起使用时，每列只允许一个空值。

因此，当表中已经有一个主键时，就可以使用唯一性约束。当使用唯一性约束时，需要考虑一下几个因素：

使用唯一性约束的字段允许为空值
一个表可以允许有多个唯一约束
唯一性约束用于强制在指定字段上创建一个唯一性索引。
默认情况下，创建的索引类型为非聚集索引

检查约束

检查约束对输入列或者整个表中的设置检查条件，以限制输入值，保证数据的完整性，检查约束通过数据的逻辑表达式确定有效值。如对各科分数的约束，可以定义一个check约束条件，将各科分数限制在有效范围之内。

在检查性约束中，要考虑以下几点：

一个列级检查约束只能与限制的表中字段有关
一个表中可以定义多个检查约束
每个create table语句中的每个字段只能定义一个检查约束
在多个字段定义检查约束，则必须将检查约束定义为表级约束
当执行Insert语句或者update语句时，检查约束将验证数据
检查约束不能抱不能包含子查询

默认值约束

默认约束在指定插入操作中如果没有提供输入值时，系统自动指定插入值，即使该值是null。当必须向表中加载一行数据但不知道某一列的值，或该值尚不存在，此时可以使用默认值约束。默认值约束可以包括常量、函数、不带变元的内建函数或者空值。使用默认约束应该注意以下几点：

每个字段只能定义一个默认约束
如果定义的默认值长于其对应字段的允许长度，则输入到表中的默认值将被截断
不能加入带有IDENTITY属性或者数据类型为timestamp（时间戳）的字段上
如果字段定义为用户定义的数据类型，而且有一个默认绑定到这个数据类型上，则不允许该字段有默认约束。

外键约束

外键约束用于==强制参照完整性==，提供单个字段或者多个字段的参照完整性。定义时，该约束参考同一个表或者另一个表中主键约束字段，而且外键表中的字段数目和每个字段指定的数据类型都必须和peferences表中的字段相匹配。当使用外键约束时，应考虑以下几个因素：

外键约束提供了字段参照完整性
外键从句中中的字段数目和每个字段指定的数据类型都必须和peferences从句中的字段相匹配
外键约束不能自动创建索引，需要用户手动创建
用户想要修改外键约束的数据，必须只使用peferences从句，不能使用外键自居
一个表中最多可以有31外键约束
在临时表中，不能使用外键约束
主键和外键的数据类型必须严格匹配

添加约束

分在资源管理器里添加和利用T-SQL语句添加

删除约束

分在资源管理器里添加和利用T-SQL语句删除

作者： 52Hz 发表日期：2021 年 9 月 7 日